package com.fanrenqiji.common.utils.excels;

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.fanrenqiji.common.utils.CSVTokenizer;
import com.fanrenqiji.common.utils.PathUtils;

/**
 * @author lijian E-mail:ljz0721cx@163.com
 * @version 创建时间：2015年3月23日 下午4:21:19
 * @description
 */
public class ExcelUtil {

	/**
	 * 设置响应
	 * 
	 * @param response
	 * @param targetName
	 */
	public static void setExcelResponse(HttpServletResponse response,
			String targetName) {
		response.reset(); // 非常重要
		try {
			targetName = new String(targetName.getBytes("gb2312"), "ISO8859-1");
			response.setHeader("content-disposition", "attachment; filename="
					+ targetName + ".xls");
			// 设定输出文件头
			response.setContentType("application/msexcel");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 导出单一的模板
	 * @param response
	 * @param targetName
	 */
	public static void exportExcelTemplate(HttpServletResponse response,
			String sourcePath, String targetName) {
		OutputStream outStream = null;
		BufferedInputStream br = null;
		try {
			// 根据传进来的文件路径、创建文件
			File file = new File(PathUtils.getRealClassPath(sourcePath));
			// 创建一个缓冲输入流对象
			br = new BufferedInputStream(new FileInputStream(file));
			byte[] buf = new byte[1024];
			int len = 0;
			response.reset(); // 非常重要
			response.setHeader("content-disposition", "attachment; filename="
					+ URLEncoder.encode(targetName, "utf-8") + ".xls");// 设定输出文件头
			response.setContentType("application/msexcel");

			// 创建输出流对象
			outStream = response.getOutputStream();
			// 开始输出
			while ((len = br.read(buf)) > 0)
				outStream.write(buf, 0, len);
			// 关闭流对象
			outStream.flush();
			br.close();
			outStream.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	
	
	public final static Object getCellValue(HSSFCell cell) 
	{ 
	  if (cell != null) 
	  { 
	   int t = cell.getCellType(); 

	   if (t == HSSFCell.CELL_TYPE_NUMERIC) 
	   { 
	    int f = cell.getCellStyle().getDataFormat(); 
	    //if (f >= 0x0A && f <= 0x16) // Datetime format 
	    if (((f >= 14 && f <= 17) || (f >= 164 && f <= 168) || (f >= 172 && f <= 177) || f == 22)) 
	     return (java.util.Date) cell.getDateCellValue(); 

	    double d = cell.getNumericCellValue(); 
	    if (d == Math.floor(d)) 
	     return new Long((long) d); 

	    return new Double(d); 
	   } 
	   else if (t == HSSFCell.CELL_TYPE_STRING) 
	    return cell.getStringCellValue(); 
	  } 

	  return null; 
	} 
	
	
	/** 
	  * transform csv file into excel file 
	  * 
	  * @param csv name of a csv file that will be transformed 
	  * @param excel name of a excel file that will store the transformed file 
	  * 
	  * @throws IOException 
	  */ 
	public final static void csvToExcel(String csv, String excel) throws IOException 
	{ 
	  HSSFWorkbook wb = new HSSFWorkbook(); 
	  HSSFSheet sheet = wb.createSheet("Sheet1"); 

	  BufferedReader r = null; 
	  
	  try 
	  { 
	   r = new BufferedReader(new FileReader(csv)); 
	  
	   int i = 0; 

	   while (true) 
	   { 
	    String ln = r.readLine(); 

	    if (ln == null) 
	     break; 

	    HSSFRow row = sheet.createRow((short) i++); 
	    int j = 0; 

	    for (CSVTokenizer it = new CSVTokenizer(ln); it.hasMoreTokens();) 
	    { 
	     String val = it.nextToken(); 

	     HSSFCell cell = row.createCell((short) j++); 
	     cell.setCellValue(val); 
	    } 
	   } 
	  } 
	  finally 
	  { 
	   if (r != null) 
	    r.close(); 
	  } 

	  FileOutputStream fileOut = null; 
	  
	  try 
	  { 
	   fileOut = new FileOutputStream("e://abc.xls"); 
	   wb.write(fileOut); 
	  } catch (FileNotFoundException e) {
		    System.err.println("获取不到位置");
		    e.printStackTrace();
		   } 
	  finally 
	  { 
	   if (fileOut != null) 
	    fileOut.close(); 
	  } 
	} 
	
	
	public static void main(String[] args) throws IOException {
		csvToExcel("E:\\test-20150919223355.csv", "www.xls");
	}

}
